﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_ImportVolunteer_SearchRegionVolunteerControl

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    Public Function BLL_Select(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As DataTable
        Return Me.BLL_SelectPage(UOrganizationID, VOrganizationID, VolunteerId, VolunteerName, TEL, -1, 0)
    End Function

    Public Function BLL_SelectPage(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal sort As String) As DataTable
        Return Me.BLL_SelectPage(UOrganizationID, VOrganizationID, VolunteerId, VolunteerName, TEL, -1, 0, sort)
    End Function

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_SelectPage(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal PageSize As Integer, ByVal StartRowIndex As Integer) As DataTable
        Return Me.BLL_SelectPage(UOrganizationID, VOrganizationID, VolunteerId, VolunteerName, TEL, PageSize, StartRowIndex, "")
    End Function

    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
        Public Function BLL_SelectBlackPage(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal PageSize As Integer, ByVal StartRowIndex As Integer) As DataTable
        Return Me.BLL_SelectBlackPage(UOrganizationID, VOrganizationID, VolunteerId, VolunteerName, TEL, PageSize, StartRowIndex, "")
    End Function

    Public Function BLL_SelectPage(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal PageSize As Integer, ByVal StartRowIndex As Integer, ByVal sort As String) As DataTable

        Dim sql As MyStringBuilder

        If StartRowIndex = -1 Then
            sql = New MyStringBuilder
            sql.CountOnly = True
        ElseIf PageSize = -1 Then
            sql = New MyStringBuilder
        Else
            sql = New MyStringBuilder(PageSize, StartRowIndex)
        End If

        Dim dat As New clsData

        sql.AppendFormat("SELECT A.VolunteerID,A.Birthday,A.Email,A.VolunteerName,A.MobilePhone,A.OfficePhone,A.BirthAddress,A.Address,A.EmergentPerson,A.EmergentPhone,B.RegionName as RegionName ")
        sql.AppendFormat(", B.TownName as TownName ")
        sql.AppendFormat(", bv.RegionName as BirthRegionName ")
        sql.AppendFormat(", bv.TownName as BirthTownName ")
        sql.AppendFormat(", TrainingBHour ")
        sql.AppendFormat(", TrainingSHour ")
        sql.AppendFormat(", TrainingWHour ")
        sql.AppendFormat(", vs.WorkHour ")
        sql.AppendFormat(", vs.OpenWorkHour ")
        sql.AppendFormat(", vs.EffectiveWorkHour ")
        'sql.AppendFormat(", WorkHour ")
        sql.AppendFormat(", cm_Gender.Text as GenderText ")
        sql.AppendFormat(", cm_EducationLevel.Text as EducationLevelText ")
        sql.AppendFormat(", cm_OccupationType.Text as OccupationTypeText ")
        sql.AppendFormat(", cm_Retired.Text as RetiredText ")
        sql.AppendFormat(", vb.VBookID ")
        sql.AppendFormat(", vb.IssueDate ")
        sql.AppendFormat(", cm_Vbook.Text+'北縣字第'+vb.SerialNumber+'號' as SerialNumber ")
        sql.AppendFormat(", oo.VOrganizationName ")
        sql.AppendFormat(",oo.DirectUOrganizationID,A.Active ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("inner join VolunteerStat vs on A.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat("left join CodeMapping cm_Gender on A.Gender = cm_Gender.Value and cm_Gender.TableName = 'Volunteer' and cm_Gender.ColumnName = 'Gender' ")
        sql.AppendFormat("left join CodeMapping cm_EducationLevel on A.EducationLevel = cm_EducationLevel.Value and cm_EducationLevel.TableName = 'Volunteer' and cm_EducationLevel.ColumnName = 'EducationLevel' ")
        sql.AppendFormat("left join CodeMapping cm_OccupationType on A.OccupationType = cm_OccupationType.Value and cm_OccupationType.TableName = 'Volunteer' and cm_OccupationType.ColumnName = 'OccupationType' ")
        sql.AppendFormat("left join CodeMapping cm_Retired on A.Retired = cm_Retired.Value and cm_Retired.TableName = 'Volunteer' and cm_Retired.ColumnName = 'Retired' ")
        sql.AppendFormat("LEFT JOIN  Village B on A.VillageId=B.RegionID ")
        sql.AppendFormat("LEFT JOIN  Village bv on A.BirthVillageId=bv.RegionID ")
        sql.AppendFormat("left join VBookView vb on A.VolunteerID = vb.VolunteerID and vb.Active = 'Y' ")
        sql.AppendFormat("left join CodeMapping cm_Vbook on cm_Vbook.Value = vb.SerialGroupID and cm_Vbook.TableName = 'VBook' and cm_Vbook.ColumnName = 'SerialGroupID' ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")


        sql.AppendFormat("WHERE A.Active in ('2','Y') ")
        sql.AppendFormat("AND (oo.VOrganizationID = {0} or {0}=0 ) ", VOrganizationID)
           If UOrganizationID <> "0" Then
            sql.AppendFormat("and (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        'sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (A.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' or '{0}'='') or (A.OfficePhone like '%{0}%' or '{0}'=''))) ", TEL)
        'MyObj.WriteLog(sql.ToString())

        If sort = "" Then
            sql.AppendFormat("order by A.VolunteerID ")
        Else
            sql.AppendFormat("order by {0} ", sort)
        End If

        'MyObj.WriteLog(PageSize)
        'MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

    Public Function BLL_SelectBlackPage(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String, ByVal PageSize As Integer, ByVal StartRowIndex As Integer, ByVal sort As String) As DataTable

        Dim sql As MyStringBuilder

        If StartRowIndex = -1 Then
            sql = New MyStringBuilder
            sql.CountOnly = True
        ElseIf PageSize = -1 Then
            sql = New MyStringBuilder
        Else
            sql = New MyStringBuilder(PageSize, StartRowIndex)
        End If

        Dim dat As New clsData

        sql.AppendFormat("SELECT A.*,B.RegionName as RegionName ")
        sql.AppendFormat(", B.TownName as TownName ")
        sql.AppendFormat(", bv.RegionName as BirthRegionName ")
        sql.AppendFormat(", bv.TownName as BirthTownName ")
        sql.AppendFormat(", TrainingBHour ")
        sql.AppendFormat(", TrainingSHour ")
        sql.AppendFormat(", TrainingWHour ")
        sql.AppendFormat(", vs.WorkHour ")
        sql.AppendFormat(", vs.OpenWorkHour ")
        sql.AppendFormat(", vs.EffectiveWorkHour ")
        'sql.AppendFormat(", WorkHour ")
        sql.AppendFormat(", cm_Gender.Text as GenderText ")
        sql.AppendFormat(", cm_EducationLevel.Text as EducationLevelText ")
        sql.AppendFormat(", cm_OccupationType.Text as OccupationTypeText ")
        sql.AppendFormat(", cm_Retired.Text as RetiredText ")
        sql.AppendFormat(", vb.VBookID ")
        sql.AppendFormat(", vb.IssueDate ")
        sql.AppendFormat(", oo.VOrganizationName ")
        sql.AppendFormat(",oo.DirectUOrganizationID ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("inner join VolunteerStat vs on A.VolunteerID = vs.VolunteerID ")
        sql.AppendFormat("left join CodeMapping cm_Gender on A.Gender = cm_Gender.Value and cm_Gender.TableName = 'Volunteer' and cm_Gender.ColumnName = 'Gender' ")
        sql.AppendFormat("left join CodeMapping cm_EducationLevel on A.EducationLevel = cm_EducationLevel.Value and cm_EducationLevel.TableName = 'Volunteer' and cm_EducationLevel.ColumnName = 'EducationLevel' ")
        sql.AppendFormat("left join CodeMapping cm_OccupationType on A.OccupationType = cm_OccupationType.Value and cm_OccupationType.TableName = 'Volunteer' and cm_OccupationType.ColumnName = 'OccupationType' ")
        sql.AppendFormat("left join CodeMapping cm_Retired on A.Retired = cm_Retired.Value and cm_Retired.TableName = 'Volunteer' and cm_Retired.ColumnName = 'Retired' ")
        sql.AppendFormat("LEFT JOIN  Village B on A.VillageId=B.RegionID ")
        sql.AppendFormat("LEFT JOIN  Village bv on A.BirthVillageId=bv.RegionID ")
        sql.AppendFormat("left join VBookView vb on A.VolunteerID = vb.VolunteerID and vb.Active = 'Y' ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID  from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationView vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")

        sql.AppendFormat("WHERE A.Active in ('2','Y') ")
        sql.AppendFormat("AND (oo.VOrganizationID = {0} or {0}=0 ) ", VOrganizationID)
        If UOrganizationID <> "0" Then
            sql.AppendFormat("and (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        'sql.AppendFormat("AND (A.VillageID IN (SELECT RegionID FROM Region WHERE Parent='{0}' or '{0}' ='' )) ", Region)
        'sql.AppendFormat("AND (A.VillageID ='{0}' or '{0}' ='' ) ", Region2)
        sql.AppendFormat("AND (A.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)
        sql.AppendFormat("AND (((A.MobilePhone like '%{0}%' or '{0}'='') or (A.OfficePhone like '%{0}%' or '{0}'=''))) ", TEL)
        'MyObj.WriteLog(sql.ToString())

        sql.AppendFormat(" And A.BlackList <> '' And A.BlackList is not null ")

        If sort = "" Then
            sql.AppendFormat("order by A.VolunteerID ")
        Else
            sql.AppendFormat("order by {0} ", sort)
        End If


        'MyObj.WriteLog(PageSize)
        'MyObj.WriteLog(sql.ToString())
        Return dat.GetData(sql)

    End Function

    Public Function BLL_SelectCount(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As Integer
        'MyObj.WriteLog("count")
        Return Me.BLL_SelectPage(UOrganizationID, VOrganizationID, VolunteerId, VolunteerName, TEL, -1, -1).Rows(0)(0)
        'Return 100
    End Function

    Public Function BLL_SelectBlackCount(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String, ByVal TEL As String) As Integer
        'MyObj.WriteLog("count")
        Return Me.BLL_SelectBlackPage(UOrganizationID, VOrganizationID, VolunteerId, VolunteerName, TEL, -1, -1).Rows(0)(0)
        'Return 100
    End Function
    Public Function BLL_ImportRecord(ByVal UOrganizationID As String, ByVal VOrganizationID As Integer, ByVal VolunteerId As String, ByVal VolunteerName As String) As DataTable

        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.Length = 0
        '不帶生日欄位 個資法 Mod by Shihyu 2011/08/19
        sql.AppendFormat("SELECT distinct A.VolunteerName,A.VolunteerID ")
        sql.AppendFormat(", cm_Vbook.Text+'新北市字第'+vb.SerialNumber+'號' as SerialNumber,A.Active ")
        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("left join VBookView vb on A.VolunteerID = vb.VolunteerID and vb.Active = 'Y' ")
        sql.AppendFormat("left join CodeMapping cm_Vbook on cm_Vbook.Value = vb.SerialGroupID and cm_Vbook.TableName = 'VBook' and cm_Vbook.ColumnName = 'SerialGroupID' ")
        sql.AppendFormat("left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")
        sql.AppendFormat("WHERE A.Active in ('2','Y') ")
        sql.AppendFormat("AND (oo.VOrganizationID = {0} or {0}=0 ) ", VOrganizationID)
        If UOrganizationID <> "0" Then
            sql.AppendFormat("and (oo.DirectUOrganizationID = '{0}'  ) ", UOrganizationID)
        End If
        sql.AppendFormat("AND (A.VolunteerId = '{0}' or '{0}'='') ", VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", VolunteerName)

        Return dat.GetData(sql)
    End Function
End Class
